import pandas as pd
import chardet

def detect_encoding(file_path):
    with open(file_path, 'rb') as file:
        result = chardet.detect(file.read())
    return result['encoding']

def process_csv(input_file, output_file):
    # 检测文件编码
    detected_encoding = detect_encoding(input_file)
    print(f"Detected encoding: {detected_encoding}")

    # 使用检测到的编码读取文件
    try:
        df = pd.read_csv(input_file, encoding=detected_encoding, skiprows=[0, 1, 2, 3, 5])
    except Exception as e:
        print(f"Error reading file with {detected_encoding} encoding: {e}")
        print("Trying with 'GBK' encoding...")
        try:
            df = pd.read_csv(input_file, encoding='GBK', skiprows=[0, 1, 2, 3, 5], errors='replace')
        except Exception as e:
            print(f"Error reading file with GBK encoding: {e}")
            return

    # 将第I列转换为yyyy/mm/dd hh:mm:ss格式
    try:
        df.iloc[:, 8] = pd.to_datetime(df.iloc[:, 8], format='%Y/%m/%d %H:%M').dt.strftime('%Y/%m/%d %H:%M:%S')
    except Exception as e:
        print(f"Error processing date column: {e}")
        return

    # 将处理后的数据保存回CSV文件
    try:
        df.to_csv(output_file, index=False, encoding='utf-8-sig')
    except Exception as e:
        print(f"Error writing to output file: {e}")

# 示例用法
input_file = r'E:\CODE\QINDONG\表格 - 原始\1、门诊费用明细查询2024.1.csv'  # 输入文件路径
output_file = r'E:\CODE\QINDONG\表格 - 原始\1.csv'  # 输出文件路径
process_csv(input_file, output_file)